package com.xmy.cultivate.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.xmy.cultivate.entity.Student;
import com.xmy.cultivate.entity.StudentGrade;
import com.xmy.cultivate.entity.views.ResLossData;
import com.xmy.cultivate.entity.views.SelectStudent;
import org.apache.ibatis.annotations.*;

import java.math.BigDecimal;
import java.util.List;

/**
 * <p>
 * 学生表 Mapper 接口
 * </p>
 *
 * @author min
 * @since 2022-07-26
 */
@Mapper
public interface StudentMapper extends BaseMapper<Student> {

    @Select("select * from student where id=#{id}")
    public abstract Student findOne(@Param("id") Long id);

    @Select("select id,CONCAT(name,remark) as name,id_card_no,school_id,head_img,sex,grade_id,class_name,school_name,address,is_guanzhu,is_gather,status,linkman,linkman_phone,level,remark,year_class_id,stu_type from student  as a ${ew.customSqlSegment}")
    public abstract IPage<Student> findAll(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);

    @Select("select count(distinct course_id) as apply_course_count from order_detail ${ew.customSqlSegment}")
    public abstract Integer getApplyCourseCount(@Param(Constants.WRAPPER) Wrapper wrapper);


    /**
     * 花名册
     * @param page
     * @param schoolId
     * @param schoolIdStr
     * @param state
     * @param isGuanzhu
     * @param isGather
     * @param isOwn
     * @param isIdCard
     * @param isCourseHouse
     * @param keywordType
     * @param keyWord
     * @return
     */
    @Select({"<script>",
                "select id,CONCAT(name,remark) as name,school_id,head_img,sex,grade_id,class_name,school_name,address,is_guanzhu,is_gather,status,linkman,linkman_phone,level,remark from student  as a",
                "where deleted = 0",
                "<when test='sourseLeve == 2'>",
                    "and id in (select student_id from student_grade_view where deleted = 0 and teacher_id =${adminId} and year_part = ${yearPart} and quarter_num=${quarterNum})",
                "</when>",
                "<when test='schoolIdStr!=null and schoolIdStr!=&apos;&apos;'>",
                 "and id in (select student_id from order_apply where school_id in(${schoolIdStr}) and deleted=0 and status&lt;&gt;2 group by student_id)",
                "</when>",
                "<when test='state!=null and state!=&apos;&apos;'>",
                    "and id in(select student_id from student_grade where status = 1 and deleted = 0 and reading_status =${state} and year_part = ${yearPart} and quarter_num=${quarterNum})",
                "</when>",
                "<when test='isGather!=null and isGather!=&apos;&apos;'>",
                "and id",
                    "<if test='isGather == 1'>",
                    "in",
                    "</if>",
                    "<if test='isGather == 0'>",
                    "not in",
                    "</if>",
                    "(",
                    "select student_id from student_face_data",
                    "<where>",
                        "<when test='schoolIdStr!=null and schoolIdStr!=&apos;&apos;'>",
                        "school_id in(${schoolIdStr})",
                        "</when>",
                    "</where>",
                ")",
                "</when>",
                "<when test='isGuanzhu!=null and isGuanzhu!=&apos;&apos;'>",
                "and is_guanzhu = ${isGuanzhu}",
                "</when>",
                "<when test='isOwn!=null and isOwn!=&apos;&apos;'>",
                    "<if test='isOwn == 1'>",
                    "and id in(select student_id from order_apply where own_price&gt;0 and status&lt;&gt;2 and is_own = ${isOwn})",
                    "</if>",
                    "<if test='isOwn == 0'>",
                    "and id in(select student_id from order_apply where own_price=0 and status&lt;&gt;2 and is_own = ${isOwn})",
                    "</if>",
                "</when>",
                "<when test='isIdCard!=null and isIdCard!=&apos;&apos;'>",
                    "<if test='isIdCard == 1'>",
                    "and id_card_no is not null",
                    "</if>",
                    "<if test='isIdCard == 0'>",
                    "and id_card_no is null",
                    "</if>",
                "</when>",
                "<choose>",
                    "<when test='isCourseHouse!=null and isCourseHouse!=&apos;&apos;'>",
                    "and id ",
                        "<if test='isCourseHouse == 1'>",
                        "in",
                        "</if>",
                        "<if test='isCourseHouse == 0'>",
                        "not in",
                        "</if>",
                        "(",
                        "select student_id from order_detail",
                            "<where>",
                                "residue_course_count&gt;0 and status&lt;&gt;2",
                                "<when test='schoolIdStr!=null and schoolIdStr!=&apos;&apos;'>",
                                " and school_id in(${schoolIdStr}) and lesson_type=1",
                                "</when>",
                                "GROUP BY student_id)",
                            "</where>",
                    "</when>",
                 "</choose>",
                "<when test='(keywordType!=null and keywordType!=&apos;&apos;) and (keyWord!=null and keyWord!=&apos;&apos;)'>",
                    "<if test='keywordType == 1'>",
                    "and name like '%${keyWord}%'",
                    "</if>",
                    "<if test='keywordType == 2'>",
                    "and linkman_phone like '%${keyWord}%'",
                    "</if>",
                "</when>",
                "<when test='isScheduling!=null and isScheduling!=&apos;&apos;'>",
                    "<if test='isScheduling == 1'>",
                        "and id in(select student_id from student_grade where school_id in(${schoolIdStr}) and status = 1 and deleted = 0 and reading_status=1 and year_part = ${yearPart} and quarter_num=${quarterNum})",
                    "</if>",
                    "<if test='isScheduling == 0'>",
                        "and id in(select student_id from order_detail where school_id in(${schoolIdStr}) and residue_price >0 and year_part = ${yearPart} and quarter_num = ${quarterNum} and student_id not in(select student_id from student_grade where school_id in(${schoolIdStr}) and year_part =${yearPart} and quarter_num = ${quarterNum} and `status` = 1 and reading_status = 1) GROUP BY student_id)",
                    "</if>",
                "</when>",
            "</script>"})
    @Results({
            @Result(column = "id", property = "id"),
            @Result(
                    property = "signUpSchoolList",
                    column = "id",
                    many = @Many(select = "com.xmy.cultivate.mapper.OrderDetailMapper.getSignUpSchoolList")
            )
    })
    public IPage<Student> rosterList(IPage<?> page,@Param("yearPart")Integer yearPart,@Param("quarterNum")Integer quarterNum,@Param("schoolId") Long schoolId,@Param("schoolIdStr") String schoolIdStr,@Param("state") String state,@Param("isGuanzhu") String isGuanzhu,@Param("isGather") String isGather,@Param("isOwn") String isOwn,@Param("isIdCard") String isIdCard,@Param("isCourseHouse") String isCourseHouse,@Param("isScheduling")String isScheduling,@Param("keywordType") String keywordType,@Param("keyWord") String keyWord,@Param("sourseLeve") Integer sourseLeve,@Param("adminId") Long adminId);

    /**
     * 花名册
     * @param page
     * @param schoolId
     * @param schoolIdStr
     * @param state
     * @param isGuanzhu
     * @param isGather
     * @param isOwn
     * @param isIdCard
     * @param isCourseHouse
     * @param keywordType
     * @param keyWord
     * @return
     */
    @Select({"<script>",
            "select id,CONCAT(name,remark) as name,school_id,head_img,sex,grade_id,class_name,school_name,address,is_guanzhu,is_gather,status,linkman,linkman_phone,level,remark from student  as a",
            "where deleted = 0",
            "<when test='studentId!=null and studentId!=&apos;&apos;'>",
            "AND id IN (${studentId})",
            "</when>",
            "<when test='sourseLeve == 2'>",
            "and id in (select student_id from student_grade_view where deleted = 0 and teacher_id =${adminId} and year_part = ${yearPart} and quarter_num=${quarterNum})",
            "</when>",
            "<when test='schoolIdStr!=null and schoolIdStr!=&apos;&apos;'>",
            "and id in (select student_id from order_apply where school_id in(${schoolIdStr}) and deleted=0 and status&lt;&gt;2 group by student_id)",
            "</when>",
            "<when test='state!=null and state!=&apos;&apos;'>",
            "and id in(select student_id from student_grade where status = 1 and deleted = 0 and reading_status =${state} and year_part = ${yearPart} and quarter_num=${quarterNum})",
            "</when>",
            "<when test='isGather!=null and isGather!=&apos;&apos;'>",
            "and id",
            "<if test='isGather == 1'>",
            "in",
            "</if>",
            "<if test='isGather == 0'>",
            "not in",
            "</if>",
            "(",
            "select student_id from student_face_data",
            "<where>",
            "<when test='schoolIdStr!=null and schoolIdStr!=&apos;&apos;'>",
            "school_id in(${schoolIdStr})",
            "</when>",
            "</where>",
            ")",
            "</when>",
            "<when test='isGuanzhu!=null and isGuanzhu!=&apos;&apos;'>",
            "and is_guanzhu = ${isGuanzhu}",
            "</when>",
            "<when test='isOwn!=null and isOwn!=&apos;&apos;'>",
            "<if test='isOwn == 1'>",
            "and id in(select student_id from order_apply where own_price&gt;0 and status&lt;&gt;2 and is_own = ${isOwn})",
            "</if>",
            "<if test='isOwn == 0'>",
            "and id in(select student_id from order_apply where own_price=0 and status&lt;&gt;2 and is_own = ${isOwn})",
            "</if>",
            "</when>",
            "<when test='isIdCard!=null and isIdCard!=&apos;&apos;'>",
            "<if test='isIdCard == 1'>",
            "and id_card_no is not null",
            "</if>",
            "<if test='isIdCard == 0'>",
            "and id_card_no is null",
            "</if>",
            "</when>",
            "<choose>",
            "<when test='isCourseHouse!=null and isCourseHouse!=&apos;&apos;'>",
            "and id ",
            "<if test='isCourseHouse == 1'>",
            "in",
            "</if>",
            "<if test='isCourseHouse == 0'>",
            "not in",
            "</if>",
            "(",
            "select student_id from order_detail",
            "<where>",
            "residue_course_count&gt;0 and status&lt;&gt;2",
            "<when test='schoolIdStr!=null and schoolIdStr!=&apos;&apos;'>",
            " and school_id in(${schoolIdStr}) and lesson_type=1",
            "</when>",
            "GROUP BY student_id)",
            "</where>",
            "</when>",
            "</choose>",
            "<when test='(keywordType!=null and keywordType!=&apos;&apos;) and (keyWord!=null and keyWord!=&apos;&apos;)'>",
            "<if test='keywordType == 1'>",
            "and name like '%${keyWord}%'",
            "</if>",
            "<if test='keywordType == 2'>",
            "and linkman_phone like '%${keyWord}%'",
            "</if>",
            "</when>",
            "<when test='isScheduling!=null and isScheduling!=&apos;&apos;'>",
            "<if test='isScheduling == 1'>",
            "and id in(select student_id from student_grade where school_id in(${schoolIdStr}) and status = 1 and deleted = 0 and reading_status=1 and year_part = ${yearPart} and quarter_num=${quarterNum})",
            "</if>",
            "<if test='isScheduling == 0'>",
            "and id in(select student_id from order_detail where school_id in(${schoolIdStr}) and residue_price >0 and year_part = ${yearPart} and quarter_num = ${quarterNum} and student_id not in(select student_id from student_grade where school_id in(${schoolIdStr}) and year_part =${yearPart} and quarter_num = ${quarterNum} and `status` = 1 and reading_status = 1) GROUP BY student_id)",
            "</if>",
            "</when>",
            "</script>"})
    @Results({
            @Result(column = "id", property = "id"),
            @Result(
                    property = "signUpSchoolList",
                    column = "id",
                    many = @Many(select = "com.xmy.cultivate.mapper.OrderDetailMapper.getSignUpSchoolList")
            )
    })
    public IPage<Student> rosterListExcel(IPage<?> page,@Param("yearPart")Integer yearPart,@Param("quarterNum")Integer quarterNum,@Param("schoolId") Long schoolId,@Param("schoolIdStr") String schoolIdStr,@Param("state") String state,@Param("isGuanzhu") String isGuanzhu,@Param("isGather") String isGather,@Param("isOwn") String isOwn,@Param("isIdCard") String isIdCard,@Param("isCourseHouse") String isCourseHouse,@Param("isScheduling")String isScheduling,@Param("keywordType") String keywordType,@Param("keyWord") String keyWord,@Param("sourseLeve") Integer sourseLeve,@Param("adminId") Long adminId,@Param("studentId") String studentId);



    /**
     * 欠费
     *
     * @param wrapper
     * @return
     */
    @Select("select sum(IFNULL(fnGetOwnForStuId(a.id),0)) as ownPrice from student  as a ${ew.customSqlSegment}")
    public BigDecimal arrearage(@Param(Constants.WRAPPER) Wrapper wrapper);


    @Select("select a.*,(select count(id) from order_detail as b where b.student_id =a.id and b.status<>2 and residue_course_count>0) as apply_course_count from student  as a ${ew.customSqlSegment}")
    public abstract IPage<Student> signUpStu(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);


    //@Select("select c.*,d.* from (select a.student_id,a.reading_status,sum(b.price) as price,sum(b.solid_price) as solid_price,sum(b.residue_price) as residue_price,sum(b.use_price) as use_price,sum(b.all_course_count) as all_course_count,sum(b.use_course_count) as use_course_count,sum(b.residue_course_count) as residue_course_count  from student_grade a LEFT JOIN order_detail b on a.order_detail_id = b.id where a.grade_id=#{gradeId}  ORDER BY a.id desc limit 0,1000) c LEFT JOIN student d on c.student_id = d.id GROUP BY c.student_id")
    @Select({"<script>",
            "SELECT",
                      "t1.student_id AS id,",
                      "t1.school_id,",
                      "t2.`name`,",
                      "t2.head_img,",
                      "t2.sex,",
                      "t2.is_guanzhu,",
                      "t2.linkman_phone,",
                      "t2.linkman,",
                      "t2.id_card_no,",
                      "t1.add_type,t1.scheduling_type,",
                      "( CASE WHEN t3.id IS NULL THEN 0 ELSE 1 END ) AS is_gather,",
                      "IFNULL(t4.arrive_count,0) as arrive_count,",
                      "IFNULL(t4.repair_count,0) as repair_count,",
                      "IFNULL(t5.id,0) as trunschool_id,",
                      "IFNULL( t6.all_course_count, 0 ) AS all_transfer_count",
                    "FROM",
                      "(",
                      "SELECT",
                        "b.id,",
                        "b.year_part,",
                        "b.quarter_num,",
                        "b.subjects_id,",
                        "b.student_id,",
                        "b.school_id,",
                        "MAX( b.add_type ) AS add_type,",
                        "MAX( b.scheduling_type ) AS scheduling_type",
                      "FROM",
                        "student_grade AS b ",
                      "WHERE",
                        "b.grade_id = ${gradeId} ",
                        "AND b.`STATUS` &lt;&gt; 3 ",
                        "AND deleted = 0 ",
                        "AND (",
                          "reading_status IN ( 0, 1, 2, 4, 5, 6 ) ",
                        "OR ( reading_status = 3 AND use_course_count > 0 )) ",
                      "GROUP BY",
                        "student_id ",
                      ") AS t1",
                      "INNER JOIN student AS t2 ON t1.student_id = t2.id",
                    "<when test='newTeacher == 1'>",
                        "INNER JOIN (",
                        "SELECT student_id,teacher_id,c.subjects_id ",
                        "FROM ",
                        "student_grade AS b ",
                        "LEFT JOIN grade AS c on b.grade_id = c.id ",
                        "WHERE ",
                        "b.`STATUS` &lt;&gt; 3 ",
                        "AND b.deleted = 0 ",
                        "AND c.deleted = 0 ",
                        "AND c.lesson_type = 1 ",
                        "AND (",
                        "b.reading_status IN ( 0, 1, 2, 4, 5, 6 ) ",
                        "OR ( b.reading_status = 3 AND b.use_course_count > 0 )) ",
                        "AND c.teacher_id = ${teacherId}",
                        "GROUP BY",
                        "student_id,c.subjects_id ",
                        ") AS t9 ON t9.student_id = t1.student_id AND t1.subjects_id = t9.subjects_id ",
                    "</when>",
                      "LEFT JOIN (select school_id,student_id,subjects_id,SUM(all_course_count) as all_course_count from order_detail where year_part = ${yearPart} and quarter_num = ${quarterNum} and `status` &lt;&gt; 2 and deleted = 0 and order_type = 5 GROUP BY school_id,student_id,subjects_id) as t6 on t1.school_id = t6.school_id and t1.student_id = t6.student_id and t1.subjects_id = t6.subjects_id",
                      "LEFT JOIN ( SELECT * FROM student_face_data WHERE deleted = 0 GROUP BY school_id, student_id ) AS t3 ON t1.school_id = t3.school_id ",
                      "AND t1.student_id = t3.student_id",
                      "LEFT JOIN (",
                      "SELECT",
                        "student_id,",
                        "sum( CASE WHEN `status` = 1 AND is_deduct_house = 1 THEN buckle_class_house ELSE 0 END ) AS arrive_count,",
                        "sum( CASE WHEN `status` = 2 AND is_repair &lt;&gt; 1 and is_deduct_money &lt;&gt; 1 THEN buckle_class_house ELSE 0 END ) AS repair_count ",
                      "FROM",
                        "record_course ",
                      "WHERE",
                        "old_grade_id = ${gradeId} ",
                        "AND year_part = ${yearPart} ",
                        "AND `quarter` = ${quarterNum} ",
                        "AND deleted = 0 ",
                      "GROUP BY",
                      "student_id ",
                      ") AS t4 ON t1.student_id = t4.student_id",
                      "LEFT JOIN (select * from student_for_data as t5 where year_part = ${upYearPart} and quarter_num = ${upQuarterNum} and deleted = 0) as t5 on t1.student_id = t5.student_id and t1.subjects_id = t5.subjects_id",
                        /*"LEFT JOIN ( ",
                        "SELECT t01.id,t01.student_id,t02.year_part,t02.quarter_num,t02.subjects_id_str FROM enroll_info AS t01 INNER JOIN order_apply AS t02 ON t01.order_id = t02.id ",
                        "WHERE ",
                        "t02.year_part = ${yearPart} ",
                        "AND t02.`quarter_num` = ${quarterNum} ",
                        "AND t02.`status` &lt;&gt; 2 ",
                        "GROUP BY t02.id ",
                        ") AS t7 ON t7.year_part = t1.year_part AND t7.quarter_num = t1.quarter_num AND t7.student_id = t1.student_id AND t7.subjects_id_str = t1.subjects_id",*/
                        "where 1=1",
                        "<when test='name!=null and name!=&apos;&apos;'>",
                        "and t2.name like '%${name}%'",
                        "</when>",
                        //"<when test='status == 1'>",
                        //    "AND t1.id IS NOT NULL AND t7.id IS NOT NULL ",
                        //"</when>",
                        //"<when test='status == 2'>",
                        //    "AND t1.id IS NOT NULL AND t7.id IS NULL ",
                        //"</when>",
                        "<when test='studentIdList !=null and studentIdList!=&apos;&apos;'>",
                            "AND t1.student_id in (${studentIdList}) ",
                        "</when>",
                        "<when test='startDate !=null and startDate!=&apos;&apos;'>",
                        "AND t1.created_at &gt;= '${startDate}' ",
                        "</when>",
                        "<when test='endDate !=null and endDate!=&apos;&apos;'>",
                        "AND t1.created_at &lt;='${endDate}' ",
                        "</when>",
            "</script>"})
    public abstract IPage<Student> getStudentForGradeId(IPage<?> page, @Param("gradeId") Long gradeId, @Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("upYearPart") Integer upYearPart, @Param("upQuarterNum") Integer upQuarterNum, @Param("name") String name, @Param("status") String status, @Param("studentIdList") String studentIdList, @Param("startDate") String startDate, @Param("endDate") String endDate, @Param("newTeacher") Integer newTeacher, @Param("teacherId") Long teacherId);


    /**
     * 班级里弹窗的待分班学员
     *
     * @param page
     * @param wrapper
     * @param schoolId
     * @param courseId
     * @return
     */
    @Select({"<script>",
            "SELECT",
                      "*,",
                      "FIND_IN_SET( grade_class_type_id, class_type_id_str ) AS is_allow ",
                    "FROM",
                      "(",
                      "SELECT",
                        "a.school_id,",
                        "a.student_id,",
                        "a.year_class_id,",
                        "a.year_part,",
                        "a.quarter_num,",
                        "a.subjects_id,",
                        "b.NAME AS student_name,",
                        "b.head_img,",
                        "a.handle_date,",
                        "IFNULL( t4.class_type_id, ${classTypeId} ) AS class_type_id,",
                        "IFNULL( t4.class_type_id_str, ${classTypeId} ) AS class_type_id_str,",
                        "${classTypeId} AS grade_class_type_id,",
                        "t4.pass_shut_num,t4.right_rade,t4.midterm_score,t4.end_term_score,t4.pass_shut_quarter_id,t4.right_rade_quarter_id,t4.midterm_quarter_id,t4.end_term_quarter_id",
                      "FROM",
                        "order_detail AS a",
                        "LEFT JOIN student AS b ON a.student_id = b.id",
                        "LEFT JOIN student_class_type AS t4 ON a.year_part = ${lastYearPart} ",
                        "AND a.quarter_num = ${lastQuarterNum} ",
                        "AND a.student_id = t4.student_id ",
                        "AND a.subjects_id = t4.subjects_id ",
                      "WHERE",
                        "a.STATUS &lt;&gt; 2 ",
                        "AND a.residue_course_count &gt; 0 ",
//                            "AND a.student_id NOT IN (",
//                                "SELECT",
//                                  "student_id ",
//                                "FROM",
//                                  "student_grade AS c",
//                                "LEFT JOIN course AS d ON c.course_id = d.id ",
//                            "WHERE ",
//                              "d.id = #{courseId} ",
//                              "and c.deleted=0 ",
//                              "and c.status=1 ",
//                              "and c.reading_status in(1,4) ",
//                              "and c.year_part =#{yearPart} ",
//                              "and d.quarter_num=#{quarterNum}) ",
                      "and a.deleted = 0 ",
                      "and a.school_id = #{schoolId} ",
                      "and a.course_id = #{courseId}  ",
                      "GROUP BY a.student_id",
                      ") as t ${ew.customSqlSegment}",
            "</script>"})
    public abstract IPage<SelectStudent> getStudentForShift(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper, @Param("schoolId") Long schoolId, @Param("courseId") Long courseId, @Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum,@Param("lastYearPart") Integer lastYearPart, @Param("lastQuarterNum") Integer lastQuarterNum,@Param("classTypeId") Long classTypeId);

    /**
     * 班级里弹窗的待分班学员(智能课或者刷题班)
     *
     * @param page
     * @param wrapper
     * @param schoolId
     * @param courseId
     * @return
     */
    @Select("select * from (select a.student_id,a.year_part,a.quarter_num,a.subjects_id,a.year_class_id,b.name as student_name,b.head_img,a.handle_date,(select class_type_id_str from student_class_type as t5 where t5.student_id = a.student_id and t5.year_part = ${yearPart} and t5.quarter_num = ${quarterNum} and t5.subjects_id = a.subjects_id limit 1)  as class_type_id_str from order_detail as a LEFT JOIN student as b on a.student_id=b.id where  a.status <>2 and a.residue_course_count>0 and a.deleted = 0 and a.school_id = #{schoolId} and a.course_id = #{courseId}  GROUP BY a.student_id)  as t ${ew.customSqlSegment}")
    public abstract IPage<SelectStudent> getStudentForShiftB(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper, @Param("schoolId") Long schoolId, @Param("courseId") Long courseId, @Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum);


    /**
     * 班级里弹窗的待分班学员
     *
     * @param page
     * @param wrapper
     * @param schoolId
     * @param courseId
     * @return
     */
    @Select("select * from (select a.student_id,b.name as student_name,a.handle_date,(select class_type_id from student_class_type as t5 where t5.student_id = a.student_id and t5.year_part = ${yearPart} and t5.quarter_num = ${quarterNum} and t5.subjects_id = a.subjects_id limit 1)  as class_type_id from order_detail as a LEFT JOIN student as b on a.student_id=b.id where  a.status <>2 and a.residue_course_count>0 and a.student_id not in(select student_id from student_grade as c LEFT JOIN course as d on c.course_id=d.id where d.id = #{courseId} and c.deleted=0 and c.status=1 and c.reading_status in(1,4) and c.year_part =#{yearPart} and d.quarter_num=#{quarterNum}) and a.deleted = 0 and a.school_id = #{schoolId} and a.course_id = #{courseId}  GROUP BY a.student_id) as t ${ew.customSqlSegment}")
    public abstract IPage<SelectStudent> getStudentForShiftForClassType(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper, @Param("schoolId") Long schoolId, @Param("courseId") Long courseId, @Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum);


    /**
     * 获取预分班学生
     *
     * @param wrapper
     * @return
     */
    @Select(" select *,FIND_IN_SET(grade_class_type_id,class_type_id_str) as is_allow from (select t1.school_id,t1.subjects_id,t1.student_id,t1.year_class_id,t1.year_part,t1.quarter_num,SUM(t1.residue_course_count) as residue_course_count,t1.year_and_quarter,t3.`name` as student_name,t3.head_img,IFNULL(t4.class_type_id,1575859909042028546) as class_type_id,IFNULL(t4.class_type_id_str,1575859909042028546) as class_type_id_str,${classTypeId} as grade_class_type_id,t4.pass_shut_num,t4.right_rade,t4.midterm_score,t4.end_term_score,t4.pass_shut_quarter_id,t4.right_rade_quarter_id,t4.midterm_quarter_id,t4.end_term_quarter_id  from order_detail as t1 LEFT JOIN (select * from student_for_data where year_part = ${yearPart} and quarter_num = ${quarterNum} and lesson_type =${lessonType} and deleted =0) as t2 on t1.student_id = t2.student_id and t1.subjects_id = t2.subjects_id LEFT JOIN student as t3 on t1.student_id = t3.id LEFT JOIN student_class_type as t4 on t1.year_part = t4.year_part and t1.quarter_num = t4.quarter_num and t1.student_id = t4.student_id and t1.subjects_id = t4.subjects_id where t1.`status`<>2 and t1.deleted = 0 and (t1.school_id in(${schoolIdStr}) or t2.id is not null) and t1.lesson_type =${lessonType} and t1.subjects_id =${subjectsId} AND t1.year_and_quarter >= ${yearAndQuarter} and t1.residue_course_count>0 group by t1.school_id,t1.student_id,t1.subjects_id) as t ${ew.customSqlSegment}")
    public IPage<SelectStudent> getAllotStudent(IPage<?> page,
                                                @Param(Constants.WRAPPER) Wrapper wrapper,
                                                @Param("schoolIdStr") String schoolIdStr,
                                                @Param("subjectsId") Long subjectsId,
                                                @Param("yearAndQuarter") Integer yearAndQuarter,
                                                @Param("yearPart") Integer yearPart,
                                                @Param("quarterNum") Integer quarterNum,
                                                @Param("lessonType") Integer lessonType,
                                                @Param("classTypeId") Long classTypeId
                                                );

    /**
     * 获取学生名字
     * @param id
     * @return
     */
    @Select("SELECT name FROM student WHERE id=#{id}")
    String getStudentName(@Param("id") Long id);

    /**
     * 排班人数
     *
     * @param schoolId
     * @param subjectsId
     * @param yearPart
     * @param quarterNum
     * @param courseTypeId
     * @return
     */
    @Select({"<script>",
            "SELECT year_class_id,school_id,COUNT(id) as count",
            "FROM ",
            "( ",
            "SELECT student_grade.* FROM student_grade",
            "LEFT JOIN ( ",
            "SELECT t1.student_id,t1.subjects_id,t2.year_part,t2.quarter_num,t1.id,t1.add_date ",
            "FROM enroll_info as t1 ",
            "INNER JOIN order_apply as t2 on t1.order_id = t2.id and t1.deleted = 0 ",
            "GROUP BY t1.order_id ",
            ") AS enroll_info ON student_grade.student_id = enroll_info.student_id ",
            "AND student_grade.year_part = enroll_info.year_part  ",
            "AND student_grade.quarter_num = enroll_info.quarter_num ",
            "AND student_grade.subjects_id = enroll_info.subjects_id ",
            "WHERE ",
            "student_grade.deleted = 0 ",
            "AND student_grade.`status` = 1 ",
            "<if test='status == 0'>",
                "AND student_grade.reading_status = 0 ",
            "</if>",
            "<if test='status == 1'>",
                "AND student_grade.is_last = 1 ",
                "AND ( student_grade.reading_status IN ( 1, 4, 5, 6 ) OR ( student_grade.reading_status = 3 AND student_grade.use_course_count > 0 )) ",
//                "AND (student_grade.reading_status IN ( 0, 1 )) ",
            "</if>",
            "<when test='yearPart!=null and yearPart!=&apos;&apos;'>",
            "AND student_grade.year_part = #{yearPart} ",
            "</when>",
            "<when test='quarterNum!=null and quarterNum!=&apos;&apos;'>",
            "AND student_grade.quarter_num = #{quarterNum} ",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "AND student_grade.subjects_id = #{subjectsId} ",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "AND student_grade.school_id IN (${schoolId})",
            "</when>",
            "<when test='courseTypeId!=null and courseTypeId!=&apos;&apos;'>",
            "AND student_grade.student_id IN (SELECT student_grade.student_id FROM grade WHERE student_grade.grade_id = grade.id AND course_type_id = #{courseTypeId} ) ",
            "</when>",
            "<when test='startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos;'>",
            "AND DATE(student_grade.created_at) BETWEEN '${startDate}' AND '${endDate}' ",
            "AND enroll_info.add_date = DATE(student_grade.created_at)",
            "AND enroll_info.id is not NULL ",
            "</when>",
            "AND student_grade.lesson_type = 1 ",
            "GROUP BY student_grade.school_id,student_grade.student_id,student_grade.subjects_id ",
            ") t1 ",
            "GROUP BY year_class_id,school_id ",
            "ORDER BY school_id,year_class_id;",
            "</script>"})
    public abstract List<Student> getStudentCount(@Param("schoolId") String schoolId, @Param("subjectsId") String subjectsId, @Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("courseTypeId") String courseTypeId, @Param("status") Integer status, @Param("startDate") String startDate, @Param("endDate") String endDate);

    /**
     * 排班人数按照年级计算合计
     *
     * @param schoolId
     * @param subjectsId
     * @param yearPart
     * @param quarterNum
     * @param courseTypeId
     * @return
     */
    @Select({"<script>",
            "SELECT SUM( count ) AS count FROM (",
            "SELECT COUNT(id) as count",
            "FROM ",
            "( ",
            "SELECT student_grade.* FROM student_grade",
            "LEFT JOIN ( ",
            "SELECT t1.student_id,t1.subjects_id,t2.year_part,t2.quarter_num,t1.id,t1.add_date ",
            "FROM enroll_info as t1 ",
            "INNER JOIN order_apply as t2 on t1.order_id = t2.id and t1.deleted = 0 ",
            "GROUP BY t1.order_id ",
            ") AS enroll_info ON student_grade.student_id = enroll_info.student_id ",
            "AND student_grade.year_part = enroll_info.year_part  ",
            "AND student_grade.quarter_num = enroll_info.quarter_num ",
            "AND student_grade.subjects_id = enroll_info.subjects_id ",
            "WHERE ",
            "student_grade.deleted = 0 ",
            "AND student_grade.`status` = 1 ",
            "<if test='status == 0'>",
            "AND student_grade.reading_status = 0 ",
            "</if>",
            "<if test='status == 1'>",
            "AND student_grade.is_last = 1 ",
            "AND ( student_grade.reading_status IN ( 1, 4, 5, 6 ) OR ( student_grade.reading_status = 3 AND student_grade.use_course_count > 0 )) ",
//                "AND (student_grade.reading_status IN ( 0, 1 )) ",
            "</if>",
            "<when test='yearPart!=null and yearPart!=&apos;&apos;'>",
            "AND student_grade.year_part = #{yearPart} ",
            "</when>",
            "<when test='quarterNum!=null and quarterNum!=&apos;&apos;'>",
            "AND student_grade.quarter_num = #{quarterNum} ",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "AND student_grade.subjects_id = #{subjectsId} ",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "AND student_grade.school_id IN (${schoolId})",
            "</when>",
            "<when test='courseTypeId!=null and courseTypeId!=&apos;&apos;'>",
            "AND student_grade.student_id IN (SELECT student_grade.student_id FROM grade WHERE student_grade.grade_id = grade.id AND course_type_id = #{courseTypeId} ) ",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos;'>",
            "AND student_grade.year_class_id = #{yearClassId} ",
            "</when>",
            "<when test='startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos;'>",
            "AND DATE(student_grade.created_at) BETWEEN '${startDate}' AND '${endDate}' ",
            "AND enroll_info.add_date = DATE(student_grade.created_at)",
            "AND enroll_info.id is not NULL ",
            "</when>",
            "AND student_grade.lesson_type = 1 ",
            "GROUP BY school_id,student_id,subjects_id ",
            ") t1 ",
            "GROUP BY year_class_id,school_id ",
            "ORDER BY school_id,year_class_id",
            ") AS tt",
            "</script>"})
    public abstract Integer getStudentCountByYearClassId(@Param("schoolId") String schoolId, @Param("subjectsId") String subjectsId,@Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum,@Param("courseTypeId") String courseTypeId,@Param("yearClassId") String yearClassId,@Param("status") Integer status, @Param("startDate") String startDate, @Param("endDate") String endDate);

    /**
     * 排班人数按照校区计算合计
     *
     * @param schoolId
     * @param subjectsId
     * @param yearPart
     * @param quarterNum
     * @param courseTypeId
     * @return
     */
    @Select({"<script>",
            "SELECT SUM( count ) AS count FROM (",
            "SELECT COUNT(id) as count",
            "FROM ",
            "( ",
            "SELECT student_grade.* FROM student_grade",
            "LEFT JOIN ( ",
            "SELECT t1.student_id,t1.subjects_id,t2.year_part,t2.quarter_num,t1.id,t1.add_date ",
            "FROM enroll_info as t1 ",
            "INNER JOIN order_apply as t2 on t1.order_id = t2.id and t1.deleted = 0 ",
            "GROUP BY t1.order_id ",
            ") AS enroll_info ON student_grade.student_id = enroll_info.student_id ",
            "AND student_grade.year_part = enroll_info.year_part  ",
            "AND student_grade.quarter_num = enroll_info.quarter_num ",
            "AND student_grade.subjects_id = enroll_info.subjects_id ",
            "WHERE ",
            "student_grade.deleted = 0 ",
            "AND student_grade.`status` = 1 ",
            "<if test='status == 0'>",
            "AND student_grade.reading_status = 0 ",
            "</if>",
            "<if test='status == 1'>",
            "AND student_grade.is_last = 1 ",
            "AND ( student_grade.reading_status IN ( 1, 4, 5, 6 ) OR ( student_grade.reading_status = 3 AND student_grade.use_course_count > 0 )) ",
//                "AND (student_grade.reading_status IN ( 0, 1 )) ",
            "</if>",
            "<when test='yearPart!=null and yearPart!=&apos;&apos;'>",
            "AND student_grade.year_part = #{yearPart} ",
            "</when>",
            "<when test='quarterNum!=null and quarterNum!=&apos;&apos;'>",
            "AND student_grade.quarter_num = #{quarterNum} ",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "AND student_grade.subjects_id = #{subjectsId} ",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "AND student_grade.school_id IN (${schoolId})",
            "</when>",
            "<when test='courseTypeId!=null and courseTypeId!=&apos;&apos;'>",
            "AND student_grade.student_id IN (SELECT student_grade.student_id FROM grade WHERE student_grade.grade_id = grade.id AND course_type_id = #{courseTypeId}) ",
            "</when>",
            "<when test='startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos;'>",
            "AND DATE(student_grade.created_at) BETWEEN '${startDate}' AND '${endDate}' ",
            "AND enroll_info.add_date = DATE(student_grade.created_at)",
            "AND enroll_info.id is not NULL ",
            "</when>",
            "AND lesson_type = 1 ",
            "GROUP BY student_grade.school_id,student_grade.student_id,student_grade.subjects_id ",
            ") t1 ",
            "GROUP BY year_class_id,school_id ",
            "ORDER BY school_id,year_class_id",
            ") AS tt",
            "</script>"})
    public abstract Integer getStudentCountBySchoolId(@Param("schoolId") String schoolId, @Param("subjectsId") String subjectsId,@Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum,@Param("courseTypeId") String courseTypeId,@Param("status") Integer status, @Param("startDate") String startDate, @Param("endDate") String endDate);


    /**
     * 获取重复数据
     * @return
     */
    @Select("select * from (select *,count(id) as count from student where deleted = 0 and id_card_no<>'' and id_card_no is not null GROUP BY id_card_no,`name`) as t where count>1")
    public List<Student> getRedoStudentList();

    /**
     * 根据身份证号获取学生信息
     * @param idCardNo
     * @param name
     * @return
     */
    @Select("select * from student where deleted = 0 and id_card_no = #{idCardNo}  and name = #{name}")
    public List<Student> getStudentListForIdCardNo(@Param("idCardNo") String idCardNo,@Param("name") String name);


    /**
     * 获取学员流失数据
     * @param subjectsId
     * @param lessonType
     * @param startQuarterId
     * @param endQuarterId
     * @return
     */
    @Select({"<script>",
            "SELECT  ",
                      "SUM(CASE WHEN before_student&gt;0 THEN 1 ELSE 0 END) as before_student_all",
                      ",SUM(CASE WHEN student_all &gt; 0 THEN 1 ELSE 0 END) as student_all",
                      ",SUM(CASE WHEN now_student &gt; 0 THEN 1 ELSE 0 END) as now_student_all",
                      ",SUM( CASE WHEN center_student &gt; 0 and now_student &gt; 0 THEN 1 ELSE 0 END ) AS old_student_count",
                      ",SUM( CASE WHEN center_student = 0 and now_student &gt; 0 THEN 1 ELSE 0 END ) AS new_student_count",
                      ",SUM( CASE WHEN center_student = 0 and now_student &gt; 0 and before_student &gt; 0 THEN 1 ELSE 0 END ) AS old_to_new_student_count",
                      ",SUM(CASE WHEN before_student&gt;0 and center_student=0 THEN 1 ELSE 0 END) as loss_count",
                      ",SUM(CASE WHEN before_student&gt;0 and center_student&gt;0 THEN 1 ELSE 0 END) as not_loss_count",
                      ",SUM(CASE WHEN before_student&gt;0 and center_student=0 and now_student&gt;0 THEN 1 ELSE 0 END) as loss_read",
                      ",SUM(CASE WHEN before_student&gt;0 and center_student=0 and now_student=0 THEN 1 ELSE 0 END) as loss_not_read",
                      "FROM  ",
                        "(  ",
                        "SELECT  ",
                          "t1.*,  ",
                          "t2.id AS quarter_id,  ",
                          "SUM(CASE WHEN t2.id &lt;${startQuarterId} and t1.use_course_count&gt;0 THEN 1 ELSE 0 END) as before_student,   ",
                          "SUM(CASE WHEN t2.id &gt;=${startQuarterId} and t2.id&lt;${endQuarterId} THEN 1 ELSE 0 END) as center_student,  ",
                          "SUM(CASE WHEN t2.id &gt;=${endQuarterId} THEN 1 ELSE 0 END) as now_student,",
                          "SUM( CASE WHEN (t2.id &lt; ${endQuarterId} AND t1.use_course_count &gt; 0) or t2.id &gt;= 14 THEN 1 ELSE 0 END ) AS student_all",
                        "FROM  ",
                          "order_detail AS t1  ",
                          "LEFT JOIN `quarter` AS t2 ON t1.year_part = t2.year_part",
                          "AND t1.quarter_num = t2.num   ",
                        "WHERE  ",
                          "t1.`status` &lt;&gt; 2",
                          "AND t1.deleted = 0",
                          "AND t1.lesson_type = ${lessonType}",
                          "AND t1.all_course_count &lt;&gt; t1.out_house",
                        "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
                        "AND t1.subjects_id = ${subjectsId}",
                        "</when>",
                        "GROUP BY  ",
                          "t1.student_id,  ",
                          "t1.subjects_id   ",
                      ") AS tb01",
            "</script>"})
    public abstract ResLossData getLossData(
                                            @Param("subjectsId") Long subjectsId,
                                            @Param("lessonType") Integer lessonType,
                                            @Param("startQuarterId") Long startQuarterId,
                                            @Param("endQuarterId") Long endQuarterId);

    /**
     * 获取所有在读和预分班的学员id
     */
    @Select("SELECT t1.student_id,t2.`name` AS student_name,t2.head_img FROM order_detail AS t1 LEFT JOIN student AS t2 ON t1.student_id = t2.id ${ew.customSqlSegment}")
    public IPage<StudentGrade> getStudentIdByReading(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);

    /**
     * 根据在读和预分班的学员id，获取出校区和课程
     */
    @Select("SELECT student_id,school_id,getSchoolName ( school_id ) AS school_name,course_id,course_name FROM order_detail WHERE lesson_type = 1 AND student_id = #{studentId} AND year_part = ${yearPart} AND quarter_num = ${quarterNum} GROUP BY course_id")
    public List<StudentGrade> getByStudentIdDetails(@Param("studentId") Long studentId,@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum);

    @Select("SELECT t3.`name` AS student_name,t1.student_id,t3.head_img FROM order_dis_clan AS t1 INNER JOIN order_apply AS t2 ON t1.order_id = t2.id INNER JOIN student t3 ON t1.student_id = t3.id WHERE t2.student_id = #{studentId} AND t2.deleted = 0 AND t2.`status` <> 2 GROUP BY t1.student_id")
    public List<StudentGrade> getKinsfolk(@Param("studentId") Long studentId);

    @Select("SELECT t1.student_id FROM order_dis_clan AS t1 INNER JOIN order_apply AS t2 ON t1.order_id = t2.id WHERE t2.student_id = #{studentId} AND t2.deleted = 0 AND t2.`status` <> 2 AND t2.order_type = 7 GROUP BY t1.student_id")
    public List<Long> getKinsfolkByStudent(@Param("studentId") Long studentId);


    /**
     *获取是否有同名的试听学员
     * @param name
     * @param schoolId
     * @return
     */
    @Select({"<script>",
            "SELECT",
                    "count(t1.id)",
                    "FROM",
                    "student AS t1",
                    "INNER JOIN student_school AS t2 ON t1.id = t2.student_id ",
                    "WHERE",
                    "t1.deleted = 0 ",
                    "AND t1.NAME = #{name} ",
                    "AND t1.stu_type = 2 ",
                    "AND t2.school_id = ${schoolId}",
            "</script>"})
    public Integer getTryStudentForNameCount(@Param("name") String name,@Param("schoolId") Long schoolId);
}
